Custom Properties for Devices

Custom properties for devices are user-defined fields that allow you to add business-specific metadata to individual endpoints. These properties help you capture details that go beyond standard N-central metrics, enabling more granular reporting and operational insights.

Examples

  • Service tier (e.g., Gold, Silver)

  • Encryption status (e.g., BitLocker enabled)

  • OS compatibility (e.g., Windows 11 readiness)

  • Contract or SLA indicators

How They Work in Analytics

The Analytics Data Warehouse stores custom properties as key-value pairs, which means each property name and its value are recorded together for easy retrieval and flexibility.

Where to Find the Latest Values

  • SRC_CUSTOM_PROPERTY_DEVICE_VALUE_LATEST

    Contains the most recent values for all device-level custom properties.

  • SRC_CUSTOM_PROPERTY_DEVICE_LATEST

    Holds the definitions of device-level custom properties, including property names and IDs.

Access Through Direct Data Access (DDA)

You can query these tables using Direct Data Access (DDA) in Snowflake to:

  • Retrieve the latest custom property values for reporting.

  • Join with other datasets using DEVICE_DK for device-level analysis.

  • Perform advanced transformations such as pivoting or creating dynamic tables to reshape data for dashboards or compliance reports.

Linking these queries to datasets where DEVICE_DK is unique ensures accurate joins and prevents duplication.

Boilerplate query for devices

Use this query to retrieve the latest value of a specific custom property for each device from the Analytics Data Warehouse. This query is designed for Direct Data Access (DDA) and can be adapted to your reporting needs. It links property definitions to their values and returns the device key, property name, and current value.

Copy 
Select v.device_dk, p.name, v.value
From SHARED_ANALYTICS_DATA.SHARED_DATA.SRC_CUSTOM_PROPERTY_DEVICE_VALUE_LATEST v
Join SHARED_ANALYTICS_DATA.SHARED_DATA.SRC_CUSTOM_PROPERTY_DEVICE_LATEST p
on p.custom_property_dk = v.custom_property_dk
where p.name = 'Your Custom Property of Interest';

Here’s what each part does:

Element Description
SELECT v.device_dk, p.name, v.value

device_dk: Unique key for each device.

p.name: Name of the custom property (e.g., “Service Tier” or “BitLocker Status”).

v.value: Current value assigned to that property for the device.
FROM SRC_CUSTOM_PROPERTY_DEVICE_VALUE_LATEST v Stores the latest values for all device-level custom properties.
JOIN SRC_CUSTOM_PROPERTY_DEVICE_LATEST p ON p.custom_property_dk = v.custom_property_dk

Contains the definitions of device-level custom properties (names, IDs).

The join links property values to their property names using custom_property_dk.
WHERE p.name = 'Your Custom Property of Interest' Filters the results to only include rows for the specified custom property.

You can safely link this query to any dataset where DEVICE_DK is unique. For more advanced scenarios, Snowflake offers powerful transformation options. For example, you can use PIVOT to turn property names into columns or create dynamic tables to automate reshaping and aggregation. These techniques help organize custom property data for reporting and analysis. The exact approach will depend on your data architecture and reporting requirements.